package com.easyJava.builder;

import com.easyJava.bean.Constants;
import com.easyJava.bean.FieldInfo;
import com.easyJava.bean.TableInfo;
import com.easyJava.utils.JsonUtils;
import com.easyJava.utils.PropertiesUtils;
import com.easyJava.utils.StringUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

/**
 * 这是一个用来获取数据库表信息的类 v
 */
public class BuilderTable {
    private static Connection connection = null;

    private static String Sql_SHOW_TABLES = "show table status";

    private static String Sql_SHOW_FIELDS = "show full fields from %s";

    private static String Sql_SHOW_KEYS = "show index from %s";

    private final static Logger logger = LoggerFactory.getLogger(BuilderTable.class);



    //这是使用jdbc来连接数据库
    static {
        String driver_name = PropertiesUtils.getProperties("db.driver.name");
        String url = PropertiesUtils.getProperties("db.url");
        String username = PropertiesUtils.getProperties("db.username");
        String password = PropertiesUtils.getProperties("db.password");
        try {
            Class.forName(driver_name);
            connection= DriverManager.getConnection(url,username,password);
        } catch (Exception e) {
            logger.error("数据库连接失败",e);
            throw new RuntimeException(e);
        }
    }

    // 获取表的信息
    public static List<TableInfo> getTables(){
        PreparedStatement ps = null;  //TODO:preparedStatement 是一个接口，用来执行sql语句
        ResultSet tableResult = null;   //TODO:ResultSet 是一个接口，用来存放查询结果

        List<TableInfo> tableInfoList = new ArrayList<>();
        try {
            ps = connection.prepareStatement(Sql_SHOW_TABLES);
            tableResult =  ps.executeQuery();
            while(tableResult.next()){
                String tableName = tableResult.getString("Name");
                String comment = tableResult.getString("comment");
                String BeanName = null;
                //要去是否拿去前置tb_的表
                if(Constants.IS_IGNORE_TABLE){
                    BeanName = tableName.substring(tableName.indexOf("_")+1);
                }
                // 去把表名的大写
                BeanName = HeadLetterToUpperCase(BeanName,true);
                String beanParamName = BeanName + Constants.SUFFIX_BEAN;
                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(tableName);
                tableInfo.setBeanName(BeanName);
                tableInfo.setBeanParamName(beanParamName);
                tableInfo.setComment(comment);
                getFields(tableInfo);  //获取字段信息
                getSetKey(tableInfo);  //获取索引信息
                getExtendFieldInfoList(tableInfo);  //获取扩展字段信息
                tableInfoList.add(tableInfo);

            }
            logger.info(JsonUtils.convert2Json(tableInfoList));
        }catch (Exception e){
                logger.error("获取表信息失败",e);
        }finally {
            if(tableResult != null){
                try {
                    tableResult.close();
                } catch (SQLException e) {
                    logger.error("关闭ResultSet失败",e);
                    throw new RuntimeException(e);
                }
            }
            if(ps !=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return tableInfoList;
    }


    //获取字段信息
    public static void getFields(TableInfo tableInfo){
        PreparedStatement ps = null;
        ResultSet fieldResult = null;

        List<FieldInfo> fieldInfoList = new ArrayList<>();
        try {
            ps = connection.prepareStatement(String.format(Sql_SHOW_FIELDS,tableInfo.getTableName()));
            fieldResult = ps.executeQuery();

            while(fieldResult.next()){
                String field = fieldResult.getString("Field");
                String SqlType = fieldResult.getString("Type");
                String comment = fieldResult.getString("Comment");
                String extra = fieldResult.getString("extra");
                FieldInfo fieldInfo = new FieldInfo();

                fieldInfo.setFieldName(field);
                fieldInfo.setPropertyName(HeadLetterToUpperCase(field,false));
                SqlType = SqlType.split("\\(")[0];
                fieldInfo.setSqlType(SqlType);
                fieldInfo.setAutoIncrement("auto_increment".equals(extra) ? true : false);
                fieldInfo.setComment(comment);
                fieldInfo.setJavaType(Type_Translate(SqlType));

                fieldInfoList.add(fieldInfo);
                if(ArrayUtils.contains(Constants.SQL_DATE_TYPES,SqlType)){
                    tableInfo.setHaveDate(true);
                }
                if(ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES,SqlType)){
                    tableInfo.setHaveDateTime(true);
                }
                if(ArrayUtils.contains(Constants.SQL_DECIMAL_TYPES,SqlType)){
                    tableInfo.setHaveBigDecimal(true);
                }
            }

            tableInfo.setFieldInfoList(fieldInfoList);
        }catch (Exception e){
            logger.error("获取字段信息失败",e);
        }finally {
            try {
                fieldResult.close();
            } catch (SQLException e) {
                logger.error("关闭ResultSet失败",e);
            }
            try {
                ps.close();
            } catch (SQLException e) {
                logger.error("关闭PreparedStatement失败", e);
            }
        }
    }

    /**
     * 获取扩展字段信息
     * @param tableInfo
     */

    public static void getExtendFieldInfoList(TableInfo tableInfo){
        List<FieldInfo> extendFieldInfoList = new ArrayList<>();
        for(FieldInfo fieldInfo :tableInfo.getFieldInfoList()){
            if(ArrayUtils.contains(Constants.SQL_STRING_TYPES,fieldInfo.getSqlType())){
                FieldInfo extendfieldInfo = new FieldInfo();
                extendfieldInfo.setFieldName(fieldInfo.getFieldName());
                extendfieldInfo.setPropertyName(fieldInfo.getPropertyName() + Constants.query_string_suffix);
                extendfieldInfo.setComment(fieldInfo.getComment() + Constants.query_string_suffix);
                extendfieldInfo.setJavaType(fieldInfo.getJavaType());
                extendfieldInfo.setSqlType(fieldInfo.getSqlType());
                extendFieldInfoList.add(extendfieldInfo);
            }
            if(ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES,fieldInfo.getSqlType()) ||ArrayUtils.contains(Constants.SQL_DATE_TYPES,fieldInfo.getSqlType())){
                DealWithDate(extendFieldInfoList,fieldInfo,Constants.query_date_start);
                DealWithDate(extendFieldInfoList,fieldInfo,Constants.query_date_end);
            }
        }
        tableInfo.setExtendFieldInfoList(extendFieldInfoList);
    }

    /**
     * 处理DateFuzzy类型
     * @param extendFieldInfoList
     * @param s
     */
    public static void  DealWithDate(List<FieldInfo> extendFieldInfoList , FieldInfo fieldInfo, String s){
        FieldInfo extendDate = new FieldInfo();
        String properStart =  fieldInfo.getPropertyName() +s;
        extendDate.setPropertyName(properStart);
        extendDate.setComment(fieldInfo.getComment() + s);
        extendDate.setJavaType(Constants.STRING_TYPE);
        extendDate.setFieldName(fieldInfo.getFieldName());
        extendDate.setSqlType(fieldInfo.getSqlType());
        extendFieldInfoList.add(extendDate);
    }

    /**
     * 获取索引信息
     * @param tableInfo
     */
    public static void getSetKey(TableInfo tableInfo){
        PreparedStatement ps = null;
        ResultSet keyResult = null;
        try{
            ps=connection.prepareStatement(String.format(Sql_SHOW_KEYS,tableInfo.getTableName()));
            keyResult = ps.executeQuery();
            //TODO:这里是获取索引信息
            while(keyResult.next()){
                int nonUnique = keyResult.getInt("Non_unique");
                String keyName = keyResult.getString("Key_name");
                String columnName = keyResult.getString("Column_name");
                if(nonUnique == 1){
                    continue;
                }
                List<FieldInfo> fieldInfos = tableInfo.getKeyIndexMap().get(keyName);
                if(null == fieldInfos){
                    fieldInfos = new ArrayList<>();
                    tableInfo.getKeyIndexMap().put(keyName,fieldInfos);
                }
                for(FieldInfo fieldInfo : tableInfo.getFieldInfoList()){
                    if(fieldInfo.getFieldName().equals(columnName)){
                        fieldInfos.add(fieldInfo);
                    }
                }
            }
        }catch (Exception e ){
            logger.error("获取索引信息失败",e);
        }finally {
            try {
                keyResult.close();
            } catch (SQLException e) {
                logger.error("关闭ResultSet失败",e);
            }
            try {
                ps.close();
            } catch (SQLException e) {
                logger.error("关闭PreparedStatement失败",e);
            }
        }
    }

    //类型转换
    private static String Type_Translate(String SqlType){
        if(ArrayUtils.contains(Constants.SQL_INT_TYPES,SqlType)) {
            return "Integer";
        }else if(ArrayUtils.contains(Constants.SQL_LONG_TYPES,SqlType)) {
            return "Long";
        }else if(ArrayUtils.contains(Constants.SQL_STRING_TYPES,SqlType)) {
            return "String";
        }else if(ArrayUtils.contains(Constants.SQL_DATE_TYPES,SqlType) || ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES,SqlType)) {
            return "Date";
        }else if(ArrayUtils.contains(Constants.SQL_DECIMAL_TYPES,SqlType)) {
            return "BigDecimal";
        }else {
            logger.error("未知的类型");
            throw new RuntimeException("未知的类型");
        }
    }


    /**
     *  表：开头，_大写 字段：_大写
     * @param str
     * @param isTable
     * @return
     */
    private static String HeadLetterToUpperCase(String str, boolean isTable){
        StringBuilder sb = new StringBuilder();
        String[] sp = str.split("_");
        //如果是表名，那么第一个字母大写
        sb.append(isTable ? StringUtils.upperLetter(sp[0]):sp[0]);
        for(int i = 1 ; i< sp.length;i++){
            sb.append(StringUtils.upperLetter(sp[i]));
        }
        return sb.toString();
    }
}
